﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Windows.Controls;
using System.Windows.Input;
using Monti.MVVM;
using Monti.MVVM.Commands;
using Monti.MVVM.Services;

namespace Peter.DataAccess.Sqlite
{
   /// <summary>
   /// Represents a sqlite database.
   /// </summary>
   public class SqliteDatabase : ViewModelBase, IDisposable
   {
      #region Fields

      private FileInfo m_FileInfo;
      private TreeViewItemViewModel m_SelectedTable;
      private bool m_BoolIsSelected;
      private bool m_BoolNeedsSaving;
      private RelayCommand m_CmdEndCellEdit;
      private RelayCommand m_CmdSelectionChanged;
      private RelayCommand m_CmdAddRow;
      private RelayCommand m_CmdDeleteSelection;
      private SQLiteTransaction m_Transaction;
      private SQLiteDataAdapter m_Adapter;
      private DataSet m_DataSet;
      private bool m_Updating;

      #endregion

      #region Setup

      /// <summary>
      /// Initializes a new sqlite database.
      /// </summary>
      public SqliteDatabase ()
      {
         this.m_DataSet = new DataSet ();
         this.SelectedRows = new ObservableCollection <DataRow> ();
         this.TableList = new ObservableCollection<TreeViewItemViewModel> ();
      }

      #endregion

      #region Properties

      /// <summary>
      /// Gets the list of tables in the database.
      /// </summary>
      public ObservableCollection<TreeViewItemViewModel> TableList { get; private set; }

      /// <summary>
      /// Gets the sqlite connection.
      /// </summary>
      public SQLiteConnection SqliteConnection { get; private set; }

      /// <summary>
      /// Gets or Sets the selected table name.
      /// </summary>
      public TreeViewItemViewModel SelectedTable
      {
         get { return this.m_SelectedTable; }
         set
         {
            var parentItem = value;
            if (parentItem != null && parentItem.Parent != null)
               parentItem = parentItem.Parent;

            if (this.m_SelectedTable != parentItem || this.Data == null)
            {
               this.SelectedRows.Clear ();
               this.m_SelectedTable = parentItem;
               this.RaisePropertyChanged ("SelectedTable");
               this.QueryData (false);
            }
         }
      }

      /// <summary>
      /// Gets or Sets the data in the selected table.
      /// </summary>
      public DataTable Data
      {
         get { return (this.SelectedTable != null) ? this.m_DataSet.Tables[this.SelectedTable.Text] : null; }
      }

      /// <summary>
      /// Gets or Sets if the database needs to be saved.
      /// </summary>
      public bool NeedsSaving
      {
         get { return this.m_BoolNeedsSaving; }
         set
         {
            this.m_BoolNeedsSaving = value;
            this.RaisePropertyChanged ("NeedsSaving");
         }
      }

      /// <summary>
      /// Gets or Sets if the database is selected.
      /// </summary>
      public bool IsSelected
      {
         get { return this.m_BoolIsSelected; }
         set
         {
            this.m_BoolIsSelected = value;
            this.RaisePropertyChanged ("IsSelected");
         }
      }

      /// <summary>
      /// Gets or Sets the path to the file.
      /// </summary>
      public string FilePath
      {
         get
         {
            if (this.m_FileInfo == null)
               return string.Empty;
            return this.m_FileInfo.FullName;
         }
         set
         {
            this.m_FileInfo = new FileInfo (value);
            this.SqliteConnection = new SQLiteConnection(this.ConnString);
            this.SqliteConnection.Open ();
            this.QueryTables ();
         }
      }

      /// <summary>
      /// Gets the file size.
      /// </summary>
      public double FileSize
      {
         get
         {
            if (this.m_FileInfo == null)
               return 0;
            this.m_FileInfo.Refresh ();
            return (this.m_FileInfo.Length / 1024f);
         }
      }

      /// <summary>
      /// Gets the name of the file.
      /// </summary>
      public string FileName
      {
         get
         {
            if (this.m_FileInfo == null)
               return string.Empty;
            return this.m_FileInfo.Name;
         }
      }

      /// <summary>
      /// Gets the command for ending a cell edit.
      /// </summary>
      public ICommand EndCellEditCommand
      {
         get { return this.m_CmdEndCellEdit ?? (this.m_CmdEndCellEdit = new RelayCommand (EditCellEdit)); }
      }

      /// <summary>
      /// Gets the command when the selection changes.
      /// </summary>
      public ICommand SelectionChangedCommand
      {
         get { return this.m_CmdSelectionChanged ?? (this.m_CmdSelectionChanged = new RelayCommand (SelectionChanged)); }
      }

      /// <summary>
      /// Gets the command for adding a new row.
      /// </summary>
      public ICommand AddRowCommand
      {
         get { return this.m_CmdAddRow ?? (this.m_CmdAddRow = new RelayCommand (AddRow, CanAddRow)); }
      }

      /// <summary>
      /// Gets the command for deleting the selection.
      /// </summary>
      public ICommand DeleteSelectionCommand
      {
         get { return this.m_CmdDeleteSelection ?? (this.m_CmdDeleteSelection = new RelayCommand (DeleteSelection, CanDeleteSelection)); }
      }

      /// <summary>
      /// Gets a connection string to a sqlite database.
      /// </summary>
      private string ConnString
      {
         get { return string.Format ("Data Source={0};Version=3;", this.FilePath); }
      }

      #endregion

      #region Methods

      /// <summary>
      /// Gets the select command text.
      /// </summary>
      /// <param name="tableName">The name of the table to generate select command for.</param>
      private string SelectText (string tableName)
      {
         return string.Format ("SELECT rowid, * FROM {0};", tableName);
      }

      /// <summary>
      /// Gets the list of tables in the database.
      /// </summary>
      private void QueryTables ()
      {
         this.TableList.Clear ();
         var commmand = this.SqliteConnection.CreateCommand ();
         commmand.CommandText = "SELECT name FROM SQLITE_MASTER where type='table';";
         using (var reader = commmand.ExecuteReader ())
         {
            while (reader.Read ())
            {
               var treeItem = new TreeViewItemViewModel (null, false) { Text = reader.GetString (0)};
               var schemaCommand = this.SqliteConnection.CreateCommand ();
               schemaCommand.CommandText = string.Format ("PRAGMA table_info({0});", treeItem.Text);
               using (var schemaReader = schemaCommand.ExecuteReader ())
               {
                  while (schemaReader.Read ())
                  {
                     treeItem.Children.Add (new TreeViewItemViewModel (treeItem)
                        { Text = schemaReader.GetString (1), Tag = SqliteColumn.StringToDataType (schemaReader.GetString (2)) });
                  }
                  schemaReader.Close ();
               }

               this.TableList.Add (treeItem);
            }
         }
      }

      /// <summary>
      /// Queries the data in the selected table.
      /// </summary>
      private void QueryData (bool refresh)
      {
         if (this.SelectedTable != null && this.TableList.Contains (this.SelectedTable))
         {
            if (refresh || !this.m_DataSet.Tables.Contains (this.SelectedTable.Text))
            {
               // TODO: LIMIT (Paging) http://stackoverflow.com/questions/784726/how-can-i-paginate-a-wpf-datagrid...
               if (this.m_Adapter == null)
                  this.m_Adapter = new SQLiteDataAdapter (this.SelectText (this.SelectedTable.Text), this.SqliteConnection);
               else
                  this.m_Adapter.SelectCommand.CommandText = this.SelectText (this.SelectedTable.Text);
               this.m_Adapter.Fill (this.m_DataSet, this.SelectedTable.Text);
            }
            this.RaisePropertyChanged ("Data");
         }
      }

      /// <summary>
      /// Occurs when a cell edit it ended.
      /// </summary>
      /// <param name="obj">Command data.</param>
      private void EditCellEdit (object obj)
      {
         var data = obj as HandlerData;
         if (data != null)
         {
            var eventArgs = data.EventArgs as DataGridCellEditEndingEventArgs;
            if (eventArgs != null)
            {
               this.NeedsSaving = true;
            }
         }
      }

      /// <summary>
      /// Occurs when the selection changes..
      /// </summary>
      /// <param name="obj">Command parameter.</param>
      private void SelectionChanged (object obj)
      {
         if (this.m_Updating) return;
         var handlerData = obj as HandlerData;
         if (handlerData != null)
         {
            var eventArgs = handlerData.EventArgs as SelectionChangedEventArgs;
            if (eventArgs != null)
            {
               if (eventArgs.AddedItems != null)
               {
                  foreach (var item in eventArgs.AddedItems)
                  {
                     var dataRowView = item as DataRowView;
                     if (dataRowView != null)
                        this.SelectedRows.Add (dataRowView.Row);
                  }
               }
               if (eventArgs.RemovedItems != null)
               {
                  foreach (var item in eventArgs.RemovedItems)
                  {
                     var dataRowView = item as DataRowView;
                     if (dataRowView != null)
                        this.SelectedRows.Remove (dataRowView.Row);
                  }
               }
            }
         }
      }

      /// <summary>
      /// Gets the selected rows.
      /// </summary>
      protected ObservableCollection<DataRow> SelectedRows { get; private set; }

      /// <summary>
      /// Checks to see if the add row command can execute.
      /// </summary>
      /// <param name="obj">Command parameter.</param>
      /// <returns>If the command can execute or not.</returns>
      private bool CanAddRow (object obj)
      {
         return this.SelectedTable != null;
      }

      /// <summary>
      /// Adds a new row to the selected table.
      /// </summary>
      /// <param name="obj">Command parameter.</param>
      private void AddRow (object obj)
      {
         var row = this.Data.Rows.Add (this.DefaultRowValues ());
         row.BeginEdit ();
         this.NeedsSaving = true;
      }

      /// <summary>
      /// Gets a list of default row values.
      /// </summary>
      /// <returns>List of default row values.</returns>
      private object[] DefaultRowValues ()
      {
         int colCount = this.Data.Columns.Count;
         var rowValues = new object[colCount];
         var cols = (from DataColumn col in this.Data.Columns
                     orderby col.Ordinal
                     select col).ToList ();
         for (int a = 0; a < colCount; a++ )
            rowValues[a] = cols[a].DefaultValue;
         return rowValues;
      }

      /// <summary>
      /// Checks to see if the delete command can execute.
      /// </summary>
      /// <param name="obj">Command parameter.</param>
      /// <returns>If the command can execute or not.</returns>
      private bool CanDeleteSelection (object obj)
      {
         return this.SelectedRows != null && this.SelectedRows.Count > 0;
      }

      /// <summary>
      /// Command to delete the selection.
      /// </summary>
      /// <param name="obj">Command parameter.</param>
      private void DeleteSelection (object obj)
      {
         this.m_Updating = true;
         foreach (var dataRow in this.SelectedRows)
            this.Data.Rows.Remove (dataRow);
         this.SelectedRows.Clear ();
         this.NeedsSaving = true;
         this.m_Updating = false;
      }

      /// <summary>
      /// Adds a new table to the database.
      /// </summary>
      /// <param name="name">Name of table to add.</param>
      /// <param name="columns">List of columns to add.</param>
      /// <returns>Error message if any.</returns>
      internal string AddNewTable (string name, IList<SqliteColumn> columns)
      {
         try
         {
            if (this.m_Transaction == null)
               this.m_Transaction = this.SqliteConnection.BeginTransaction ();
            var command = this.SqliteConnection.CreateCommand ();
            string query = string.Format ("CREATE TABLE {0} (", name);
            query = columns.Aggregate (query, (current, column) => current + (column.Name + " " + column.DataType + ", "));
            query = query.Replace (SqliteColumnType.IntegerPrimaryKey.ToString (), "INTEGER PRIMARY KEY");
            query = query.Trim ().TrimEnd (',') + ");";
            command.CommandText = query;
            command.ExecuteNonQuery ();
            this.NeedsSaving = true;
         }
         catch (SQLiteException ex)
         {
            return ex.Message;
         }

         // Add it to the list...
         var treeItem = new TreeViewItemViewModel (null, false) { Text = name };
         foreach (var column in columns)
            treeItem.Children.Add (new TreeViewItemViewModel (treeItem) { Text = column.Name, Tag = column.DataType });
         this.TableList.Add (treeItem);
         return string.Empty;
      }

      /// <summary>
      /// Drops the selected table.
      /// </summary>
      internal void DropSelectedTable ()
      {
         if (this.m_Transaction == null)
            this.m_Transaction = this.SqliteConnection.BeginTransaction ();
         var command = this.SqliteConnection.CreateCommand ();
         command.CommandText = string.Format ("DROP TABLE {0};", this.SelectedTable.Text);
         command.ExecuteNonQuery ();
         this.TableList.Remove (this.TableList.First (item => item.Text == this.SelectedTable.Text));
         this.NeedsSaving = true;
      }

      /// <summary>
      /// Saves the database.
      /// </summary>
      public void Save ()
      {
         if (this.m_Adapter != null)
         {
            foreach (DataTable table in this.m_DataSet.Tables)
            {
               this.m_Adapter.SelectCommand.CommandText = this.SelectText (table.TableName);
               var commandBuilder = new SQLiteCommandBuilder (this.m_Adapter);
               this.m_Adapter.InsertCommand = commandBuilder.GetInsertCommand ();
               this.m_Adapter.UpdateCommand = commandBuilder.GetUpdateCommand ();
               this.m_Adapter.DeleteCommand = commandBuilder.GetDeleteCommand ();
               this.m_Adapter.Update (table);
            }
         }
         if (this.m_Transaction != null)
         {
            this.m_Transaction.Commit ();
            this.m_Transaction.Dispose ();
            this.m_Transaction = null;
         }
         this.RaisePropertyChanged("FileSize");
         this.NeedsSaving = false;
      }

      /// <summary>
      /// Undoes any changes that require a save.
      /// </summary>
      public void UndoChanges ()
      {
         if (this.m_Transaction != null)
         {
            this.m_Transaction.Rollback ();
            this.m_Transaction.Dispose ();
            this.m_Transaction = null;
            this.QueryTables ();
            this.QueryData (false);
         }
         this.NeedsSaving = false;
      }

      /// <summary>
      /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
      /// </summary>
      public void Dispose ()
      {
         if (this.NeedsSaving)
         {
            var messageBox = ServiceManager.Resolve<IMessageBoxService> ();
            var message = string.Format ("{0} has not been saved, would you like to save it before closing?", this.FileName);
            if (messageBox.ShowYesNo (message, "Unsaved File"))
               this.Save ();
            else
               this.UndoChanges ();
         }
         if (this.m_Adapter != null)
         {
            this.m_Adapter.Dispose ();
            this.m_Adapter = null;
         }
         if (this.m_DataSet != null)
         {
            this.m_DataSet.Dispose ();
            this.m_DataSet = null;
         }
         this.SqliteConnection.Close ();
         this.SqliteConnection.Dispose ();
      }

      /// <summary>
      /// Refreshes the data.
      /// </summary>
      public void RefreshData ()
      {
         this.QueryTables ();
         this.QueryData (true);
      }

      /// <summary>
      /// Performs a copy operation.
      /// </summary>
      /// <param name="query">Query to perform.</param>
      /// <returns>A reader that links to requested data.</returns>
      internal DataTable GetData (string query)
      {
         var table = new DataTable();
         var command = this.SqliteConnection.CreateCommand ();
         command.CommandText = query;
         using (var reader = command.ExecuteReader ())
         {
            table.Load (reader);
            reader.Close ();
         }
         return table;
      }

      /// <summary>
      /// Executes the given query.
      /// </summary>
      /// <param name="query">Query to perform.</param>
      internal string ExecuteSql (string query)
      {
         string error = string.Empty;
         if (this.m_Transaction == null)
            this.m_Transaction = this.SqliteConnection.BeginTransaction ();
         try
         {
            var commands = query.Split (';');
            foreach (var item in commands)
            {
               var command = this.SqliteConnection.CreateCommand ();
               command.CommandText = item;
               command.ExecuteNonQuery ();
               this.NeedsSaving = true;
            }
         }
         catch (SQLiteException ex)
         {
            error = ex.Message;
         }
         return error;
      }

      #endregion
   }
}
